package server.dao;

import common.bodies.GroupBody;
import common.bodies.GroupShipBody;
import common.bodies.NotificationBody;
import common.bodies.SearchResultGroupBody;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class GroupDao {
    public List<Integer> selectUsersInGroup(Integer groupId) {
        String sql = "select member_id from chat_room.group_ship where group_id = ?";

        List<Integer> userList = null;
        try (Connection conn = SQLConnection.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, groupId);
            ResultSet rs = pstmt.executeQuery();
            userList = new ArrayList<>();
            while (rs.next()) {
                Integer userId = rs.getInt("member_id");
                userList.add(userId);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    public List<GroupShipBody> getGroupList(Integer userId) {
        List<GroupShipBody> groups = new ArrayList<>();
        String sql = "SELECT gs.group_id, gs.group_name, gs.status  AS group_status,gs.role AS member_role, g.status AS member_status " +
                "FROM group_ship gs " +
                "JOIN `groups` g ON gs.group_id = g.id " +
                "WHERE gs.member_id = ? AND gs.status = 1";

        try (Connection conn = SQLConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Integer groupId = rs.getInt("group_id");
                String groupName = rs.getString("group_name");
                Integer groupStatus = rs.getInt("group_status");
                Integer memberRole = rs.getInt("member_role");
                Integer memberStatus = rs.getInt("member_status");

                if (groupStatus == 1 && memberStatus == 1) {
                    List<String> memberNames = getGroupMembers(groupId);
                    GroupShipBody group = new GroupShipBody();
                    group.setGroupId(groupId);
                    group.setGroupName(groupName);
                    group.setMemberNames(memberNames);
                    group.setRole(memberRole);
                    groups.add(group);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return groups;
    }


    // 查询群聊的成员列表
    public List<String> getGroupMembers(Integer groupId) {
        List<String> memberNames = new ArrayList<>();
        String sql = "SELECT u.name FROM group_ship gs " +
                "JOIN chat_room.users u ON gs.member_id = u.id " +
                "WHERE gs.group_id = ? AND gs.status = 1";

        try (Connection conn = SQLConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, groupId);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String username = rs.getString("name");
                memberNames.add(username);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return memberNames;
    }

    public boolean createGroup(String groupName, int creatorId) {
        Connection conn = null;
        PreparedStatement insertGroupStmt = null;
        PreparedStatement insertShipStmt = null;
        ResultSet generatedKeys = null;

        try {
            conn = SQLConnection.getConnection();
            conn.setAutoCommit(false); // 开启事务


            String insertGroupSql = "INSERT INTO chat_room.`groups` "
                    + "(group_name, manager_id,status)"
                    + " VALUES (?, ? , ?)";
            insertGroupStmt = conn.prepareStatement(insertGroupSql, Statement.RETURN_GENERATED_KEYS);
            insertGroupStmt.setString(1, groupName);
            insertGroupStmt.setInt(2, creatorId);
            insertGroupStmt.setBoolean(3, true);
            insertGroupStmt.executeUpdate();


            generatedKeys = insertGroupStmt.getGeneratedKeys();
            if (!generatedKeys.next()) {
                conn.rollback();
                return false;
            }
            int groupId = generatedKeys.getInt(1);


            String insertShipSql = "INSERT INTO group_ship"
                    + " (member_id, group_id, role, status, group_name)"
                    + " VALUES (?, ?, ?, ?, ?)";
            insertShipStmt = conn.prepareStatement(insertShipSql);
            insertShipStmt.setInt(1, creatorId);
            insertShipStmt.setInt(2, groupId);
            insertShipStmt.setInt(3, 2);
            insertShipStmt.setInt(4, 1);
            insertShipStmt.setString(5, groupName);
            insertShipStmt.executeUpdate();

            conn.commit();
            return true;

        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (conn != null) conn.rollback();
            } catch (Exception ignored) {
            }
            return false;

        } finally {
            try {
                if (generatedKeys != null) generatedKeys.close();
            } catch (Exception ignored) {
            }
            try {
                if (insertGroupStmt != null) insertGroupStmt.close();
            } catch (Exception ignored) {
            }
            try {
                if (insertShipStmt != null) insertShipStmt.close();
            } catch (Exception ignored) {
            }
            try {
                if (conn != null) conn.close();
            } catch (Exception ignored) {
            }
        }
    }

    public List<SearchResultGroupBody> searchGroups(Integer userId, String groupName) {
        List<SearchResultGroupBody> result = new ArrayList<>();

        String searchSql = "SELECT id, group_name, manager_id FROM chat_room.groups WHERE `groups`.group_name LIKE ?";
        String relSql = "SELECT group_id, status FROM chat_room.group_ship WHERE member_id = ?";

        try (Connection conn = SQLConnection.getConnection(); PreparedStatement searchStmt = conn.prepareStatement(searchSql); PreparedStatement relStmt = conn.prepareStatement(relSql)) {


            Map<Integer, Integer> groupRelationMap = new HashMap<>();
            relStmt.setInt(1, userId);
            ResultSet relRs = relStmt.executeQuery();
            while (relRs.next()) {
                int groupId = relRs.getInt("group_id");
                int status = relRs.getInt("status");
                groupRelationMap.put(groupId, status);
            }
            relRs.close();


            searchStmt.setString(1, "%" + groupName + "%");
            ResultSet searchRs = searchStmt.executeQuery();


            while (searchRs.next()) {
                int groupId = searchRs.getInt("id");
                String name = searchRs.getString("group_name");
                int managerId = searchRs.getInt("manager_id");

                int status;
                if (managerId == userId) {
                    status = 2;
                } else {
                    status = groupRelationMap.getOrDefault(groupId, -1);
                }

                result.add(new SearchResultGroupBody(groupId, name, managerId, status));
            }

            searchRs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public Boolean updateGroupShip(GroupShipBody ship) {
        String checkSql = "SELECT COUNT(*) FROM group_ship WHERE member_id = ? AND group_id = ?";
        String insertSql = "INSERT INTO group_ship (member_id, group_id, group_name, role, status) VALUES (?, ?, ?, ?, ?)";
        String updateSql = "UPDATE group_ship SET group_name = ?, role = ?, status = ? WHERE member_id = ? AND group_id = ?";

        try (Connection conn = SQLConnection.getConnection()) {
            // 先查有没有这条关系
            try (PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
                checkStmt.setInt(1, ship.getMemberId());
                checkStmt.setInt(2, ship.getGroupId());

                ResultSet rs = checkStmt.executeQuery();
                rs.next();
                int count = rs.getInt(1);

                if (count == 0) {
                    // 没有关系 就·插入
                    try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                        insertStmt.setInt(1, ship.getMemberId());
                        insertStmt.setInt(2, ship.getGroupId());
                        insertStmt.setString(3, ship.getGroupName());
                        insertStmt.setInt(4, ship.getRole());
                        insertStmt.setInt(5, ship.getStatus());

                        int inserted = insertStmt.executeUpdate();
                        return inserted > 0;
                    }
                } else {
                    // 有关系 就更新
                    try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
                        updateStmt.setString(1, ship.getGroupName());
                        updateStmt.setInt(2, ship.getRole());
                        updateStmt.setInt(3, ship.getStatus());
                        updateStmt.setInt(4, ship.getMemberId());
                        updateStmt.setInt(5, ship.getGroupId());

                        int updated = updateStmt.executeUpdate();
                        return updated > 0;
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<NotificationBody> groupRequestList(Integer userId) {
        List<NotificationBody> notifications = new ArrayList<>();
        String sql = "SELECT a.member_id, u.name, g.id, g.group_name " +
                "FROM group_ship a, users u, `groups` g, group_ship m " +
                "WHERE a.member_id = u.id " +
                "AND a.group_id = g.id " +
                "AND g.id = m.group_id " +
                "AND m.role = 2 " +
                "AND m.member_id = ? " +
                "AND a.status = 0";
        try (Connection conn = SQLConnection.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                NotificationBody noti = new NotificationBody();
                noti.setType("group");
                noti.setFromId(rs.getInt(1));
                noti.setFromName(rs.getString(2));
                noti.setToGroupId(rs.getInt(3));
                noti.setGroupName(rs.getString(4));
                noti.setGroupMangerId(userId);
                noti.setToUserId(null);
                noti.setToUserName(null);
                noti.setStatus(0);
                notifications.add(noti);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return notifications;


    }

    public void updateGroupShip(Integer fromId, Integer groupId, int role, int status) {
        String sql = "update group_ship set  role = ?, status = ?"
                + " where member_id = ? and group_id = ? ";
        try (Connection conn = SQLConnection.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, role);
            ps.setInt(2, status);
            ps.setInt(3, fromId);
            ps.setInt(4, groupId);

            int rows = ps.executeUpdate();
            if (rows > 0) {
                System.out.println("更新群关系成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateGroup(GroupBody group) {
        String sql = "update chat_room.groups" +
                " set status = ? ,group_name = ?,manager_id = ?" +
                " where id = ?";
        try (Connection conn = SQLConnection.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setBoolean(1, group.getStatus());
            ps.setString(2, group.getGroupName());
            ps.setInt(3, group.getManagerId());
            ps.setInt(4, group.getId());
            int rows = ps.executeUpdate();
            if (rows > 0) {
                System.out.println("更新群关系成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public List<String> getMutedMembers(Integer groupId) {
        List<String> mutedMembers = new ArrayList<>();
        String sql = "SELECT u.name " +
                "FROM group_ship g " +
                "JOIN users u ON g.member_id = u.id " +
                "WHERE g.group_id = ? AND g.status = 1 AND g.role = -1";

        try (Connection conn = SQLConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, groupId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    mutedMembers.add(rs.getString("name"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();

        }
        return mutedMembers;
    }
}